package com.study.dao;

import com.study.bean.Stu;
import com.study.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StuDao {
    private QueryRunner qr = DruidUtils.getQueryRunner();

    public int updateStu(Stu stu){
        String sql = "update stu set sname=?,gender=?,birthday=?,hobby=?,photo=? where id=?";
        int row = 0;
        try {
            row = qr.update(sql, stu.getSname(), stu.getGender(), stu.getBirthday(), stu.getHobby(), stu.getPhoto(), stu.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }
    public Stu queryById(Integer id){
        String sql = "select * from stu where id =?";
        Stu stu = null;
        try {
            stu = qr.query(sql, new BeanHandler<>(Stu.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return stu;
    }

    
    public  int delStu(Integer id){
        String sql = "delete from stu where id=?";
        int row = 0;
        try {
            row = qr.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }


    public  int addStu(Stu stu){
        String sql = "insert into stu(sname,gender,birthday,hobby,photo) values(?,?,?,?,?)";
        int row = 0;
        try {
            row = qr.update(sql, stu.getSname(), stu.getGender(), stu.getBirthday(), stu.getHobby(), stu.getPhoto());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return row;
    }

    public long getCountNum(Stu stu){
        StringBuilder sb = new StringBuilder("select count(id) from stu where 1=1 ");
        String sql = sqlConcat(stu, sb);
        Long count = null;
        try {
            count = qr.query(sql, new ScalarHandler<Long>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  count;
    }

    private String sqlConcat(Stu stu, StringBuilder sb) {
        String sname = stu.getSname();
        if(sname != null && !"".equals(sname.trim())){
        sb.append("  and sname like '%"+sname+"%' ");
        }
        String gender = stu.getGender();
        if(gender != null && !"-1".equals(gender) && !"".equals(gender.trim())){
            sb.append("  and gender ='"+gender+"' ");
        }
        return sb.toString();
    }

    public List<Stu> getPageList(Stu student, int start, int num) {
        StringBuilder sb = new StringBuilder("select * from stu where 1=1 ");
        // 条件的拼接
        sqlConcat(student, sb);
        // 分页参数的拼接
        sb.append(" limit ?,? ");
        String sql = sb.toString();
        System.out.println("sql = " + sql);
        List<Stu> pageList = null;
        try {
            pageList = qr.query(sql, new BeanListHandler<>(Stu.class), start, num);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
         return pageList;
    }

}
